package henu.dao.impl;

import henu.bean.Exam;
import henu.bean.Exercise;
import henu.dao.ExamDao;
import henu.dao.ExerciseDao;
import henu.dao.factory.DaoFactory;
import henu.util.Dbcp;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

public class ExerciseDaoImpl implements ExerciseDao {
	
	public static void main(String[] args) {
		ExerciseDaoImpl impl = new ExerciseDaoImpl();
		boolean t = impl.delScore(27, "412725199209012345");
		System.out.println(t);
	}
	/**
	 * @param sql
	 *            查询语句
	 * @param param
	 *            查询的参数
	 * @param result
	 *            返回操作成功的个数
	 * */
	@Override
	public int save(Exercise exercise) {
		// Exam exam = new Exam();
		QueryRunner runner = DaoFactory.getRunner();
		int result = 0;
		String sql = "INSERT INTO exercise(idcard,qid,score,eid) VALUES(?,?,?,?)";
		Object[] params = {exercise.getIdcard(),exercise.getQid(),exercise.getScore(),exercise.getEid()};
		try {
			result = runner.update(sql, params);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		return result;
	}
	@Override
	public int delete(String id) {
		// DELETE FROM Exam WHERE qid=1 ;
		String sql = "DELETE FROM Exam WHERE eid=?";
		int result = 0;
		QueryRunner runner = new QueryRunner(Dbcp.getDataSource());
		try {
			result = runner.update(sql, id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public List<Exam> findAll(String order, String sort, String property,
			String key) {
		List<Exam> list = null;
		QueryRunner runner = DaoFactory.getRunner();
		String sql = "SELECT * FROM Exam where " + property + " = ? "
				+ " ORDER BY  " + order + " " + sort;
		// System.out.println("final-sql:"+sql);
		try {
			list = runner.query(sql, new BeanListHandler<Exam>(
					Exam.class), key);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * @param property 要查询的字段名称
	 * @param key
	 * @param order
	 *            排序字段
	 * @param sort
	 *            排序方式
	 * @return
	 */
	@Override
	public List<Exam> findByProperty(String property, String key,
			String order, String sort, int start, int end) {
		List<Exam> list = null;
		String sql = "SELECT * FROM (SELECT tt.*, ROWNUM ro FROM (select * from Exam where "
				+ property
				+ "='"
				+ key
				+ "'  order by "
				+ order
				+ " "
				+ sort
				+ ") tt WHERE ROWNUM <=" + end + ") WHERE ro > " + start + "";
		QueryRunner runner = DaoFactory.getRunner();
		try {
			list = runner.query(sql, new BeanListHandler<Exam>(
					Exam.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;

	}

	/*
	 * 这个方法要求的是根据id 这个可以理解为随机获取一张卷子
	 */
	@Override
	public List<Exam> getExam(String[] id) {
		// findById();
		List<Exam> list = new ArrayList<Exam>();
		for (int i = 0; i < id.length; i++) {
			list.add(findById(id[i]));
		}
		return list;
	}

	@Override
	public int update(String id, Exam exam) {
		// UPDATE Exam SET content ='河大建校时间' ,answer ='1912'
		// ,SUBJECT='语文',TYPE='常识' WHERE qid=1 ;
		String sql = "UPDATE  Exam SET examname = ?,content =?,duration= ?,judgecount=? ,Selectcount=?,Judgescore=?,Selectscore=?,Credit=?,Examtime =? , Status =? , Examtype =? , Subject =?  WHERE eid= "
				+ id;
		// System.out.println("sql:"+sql);
		int result = 0;
		Object[] params = {exam.getExamname(),exam.getContent(),exam.getDuration(),exam.getJudgecount(),exam.getSelectcount(),exam.getJudgescore(),exam.getSelectscore(),exam.getCredit(),exam.getExamtime(),exam.getStatus(),exam.getExamtype(),exam.getSubject()};
		QueryRunner runner =DaoFactory.getRunner();
		try {
			result = runner.update(sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public Exam findById(String eid) {
			// 此处返回是bean ,查出来的所有值赋值给bean然后把bean返回
			Exam exam = null;
			String sql = "select * from Exam where eid=?";
			QueryRunner runner =DaoFactory.getRunner();
			try {
				exam = runner.query(sql,new BeanHandler<Exam>(Exam.class), eid);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return exam;
	}
	public int sumScore(String idcard,int eid) {
	/*
	 * 给我一个学生的id号码 我根据这个学生的id把他考试的总分计算出来 
	 * */
		Object[] score = null  ;
		String  sql  ="SELECT SUM(score) from EXERCISE where EID =? AND IDCARD = ?";
		QueryRunner runner = DaoFactory.getRunner();
		try {
			score = runner.query(sql, new ArrayHandler(),eid,idcard) ;
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		//System.out.println("the score is:"+score[0]);
		if(score[0]==null){								//没有分配内存之前用 ＝＝ 分配内存之后用 equals
				return 0 ; 
		}
		return Integer.parseInt(String.valueOf(score[0])) ; 
	}
	@Override
	public int[] tongji(int eid) {
			int[] studentcount = new int[5] ;  
			List<String> allidCard = findAllUser();
			int stag5  = 0; 
			int stag6  = 0; 
			int stag7  = 0; 
			int stag8  = 0; 
			int stag9  = 0; 
			int score = 0 ;
			for(String idCard:allidCard){
				  score = sumScore(idCard,eid);
					 //System.out.println(score);
					 // 把分钟除以 10 
				 score = score/10 ; 
				 switch(score){
				 case 0 : ;
				 case 1 : ;
				 case 2 : ;
			     case 3 : ;
				 case 4 : ;
				 case 5 : stag5++ ;
					 break;
				 case 6 :stag6++ ; 
				 break;
				 case 7 :stag7++ ; 
				 break;
				 case 8 :stag8++ ; 
				 break;
				 case 9 : ; 
				 case 10 :stag9++ ; 
				 break;
				}
		}
		studentcount[0] = stag5 ;
		studentcount[1] = stag6 ;
		studentcount[2] = stag7 ;
		studentcount[3] = stag8 ;
		studentcount[4] = stag9 ;
		return studentcount;
	}

	@Override
	public List<String> findAllUser() {
		QueryRunner runner = DaoFactory.getRunner();
		String sql= "SELECT idcard from PINFORMATION";
		List<String> result = new ArrayList<String>();
		List<Object[]> allid_list = null ;
		try {
			allid_list = runner.query(sql, new ArrayListHandler());
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		String id_str = null ; 
		//遍历List 取出所有id
		for(Object[] temp:allid_list){
			for(Object ob:temp){
				id_str = new String(String.valueOf(ob));
				//System.out.println("ob:"+id_str);
				result.add(id_str);
			}
		}
		return result;
	}

	@Override
	public boolean delScore(int eid, String idCard) {
		//删除一个用户某次考试的分数，先查到那次考试他做了几道题目
		int count = 0 ;
		int delnum = 0;
		QueryRunner runner = DaoFactory.getRunner();
		String getcount = "SELECT count(*) from EXERCISE where EID = "+eid+ " and IDCARD = ?"; 
		String sql = "DELETE from EXERCISE where EID = "+eid +" and IDCARD = ? ";  
		ArrayHandler ah = new ArrayHandler();
		try {
			Object[] ob= runner.query(getcount,ah,idCard);
			Number num = (Number) ob[0];
			count = num.intValue();
			delnum = runner.update(sql,idCard);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if(delnum == count){ //删除成功
			return true;
		}else{ //删除失败
			return false;
		}
	}
}
